﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace tpsmi1002
{
    class Database
    {
        private OracleConnection conn;
        private string connectionString;
        private static Database instance;
        private static readonly object instanceLock = new object();

        private Database()
        {
            connectionString = "Data Source=(DESCRIPTION="
                             + "(ADDRESS=(PROTOCOL=TCP)(HOST=neptune.uqtr.ca)(PORT=1521))"
                             + "(CONNECT_DATA=(SID=coursbd))); "
                             + "User Id=SMI1002_44; Password=72bdtv27;";

            conn = new OracleConnection(); 
            conn.ConnectionString = connectionString;

            Open();
        }

        public static Database getInstance()
        {
            lock (instanceLock)
            {
                if (instance == null)
                    instance = new Database();

                return instance;
            }
        }

        public DataSet ExecuteDataSet(string sql)
        {
            try
            {
                DataSet ds = new DataSet();
                OracleDataAdapter da = new OracleDataAdapter(sql, conn);
                da.Fill(ds, "result");

                return ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            
            return null;
        }

        public OracleDataReader ExecuteReader(string sql)
        {
            try
            {
                OracleDataReader reader;
                OracleCommand cmd = new OracleCommand(sql, conn);
                reader = cmd.ExecuteReader();

                return reader;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            return null;
        }

        public int ExecuteNonQuery(string sql)
        {
            try
            {
                int affected;
                OracleTransaction mytransaction = conn.BeginTransaction();
                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Connection = conn;
                affected = cmd.ExecuteNonQuery();
                mytransaction.Commit();

                return affected;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            return -1;
        }

        private bool Open()
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            return false;
        }

        private void Close()
        {
            conn.Close();
            conn.Dispose();
        }
    }
}